package cn.desk.database.dao.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import cn.desk.database.dao.IDatabaseDao;
import cn.desk.database.entity.ColumnInfo;
import cn.desk.database.entity.TableInfo;

public class DatabaseDaoImpl<T extends TableInfo> extends HibernateDaoSupport
		implements IDatabaseDao<T> {
	private static final Logger logger = LoggerFactory
			.getLogger(DatabaseDaoImpl.class);

	@SuppressWarnings("deprecation")
	@Override
	public List<TableInfo> listAllTableView(T t) throws SQLException {
		logger.debug("listAllTableView start");
		List<TableInfo> tableInfos = null;
		Connection con = null;
		try {
			con = getSession().connection();

			DatabaseMetaData dmd = con.getMetaData();
			String[] objectType = new String[] { "TABLE", "VIEW" };
			if (t.getTableType() instanceof String) {
				if (t.getTableType().equals("")) {
					objectType = new String[] { "TABLE", "VIEW" };
				} else {
					objectType = new String[] { t.getTableType().toUpperCase() };
				}
			}

			ResultSet tableRet = dmd.getTables(null, "%",
					"%" + t.getId() + "%", objectType);
			tableInfos = new ArrayList<TableInfo>();

			TableInfo table = null;
			while (tableRet.next()) {
				table = new TableInfo();
				table.setId(tableRet.getString(3));
				table.setTableType(tableRet.getString(4));
				tableInfos.add(table);
			}
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				con.close();
			}
		}
		logger.debug("listAllTableView end");
		return tableInfos;
	}

	@SuppressWarnings({ "deprecation", "unchecked" })
	public T load(T t) throws SQLException {
		logger.debug("load start");
		Connection con = null;
		T table = null;
		try {
			con = getSession().connection();

			DatabaseMetaData dmd = con.getMetaData();

			ResultSet tableRet = dmd.getTables(null, "%", t.getId(),
					new String[] { "TABLE", "VIEW" });
			if (tableRet.next()) {
				table = (T) new TableInfo();
				table.setId(tableRet.getString(3));
				table.setTableType(tableRet.getString(4));
			}
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				con.close();
			}
		}
		logger.debug("load end");
		return table;
	}

	@SuppressWarnings("deprecation")
	@Override
	public List<ColumnInfo> listAllColumn(String tableName) throws SQLException {
		logger.debug("listAllColumn start");
		Connection con = null;
		List<ColumnInfo> columnInfos = null;
		try {
			con = getSession().connection();

			DatabaseMetaData dmd = con.getMetaData();
			ResultSet tableRet = dmd.getColumns(null, null, tableName, null);
			columnInfos = new ArrayList<ColumnInfo>();
			ColumnInfo column = null;
			while (tableRet.next()) {
				column = new ColumnInfo();
				column.setId(tableRet.getString(4));
				column.setJdbcType(tableRet.getString(6));
				column.setLength(tableRet.getInt(7));
				columnInfos.add(column);
			}
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} finally {
			if (con != null) {
				con.close();
			}
		}
		logger.debug("listAllColumn end");
		return columnInfos;
	}

	@SuppressWarnings("deprecation")
	@Override
	public List<List<String>> executSql(String sql) throws SQLException {
		logger.debug("executSql start");
		Connection con = getSession().connection();
		try {
			Statement statement = con.createStatement();
			StringBuffer tempSql = new StringBuffer(sql.trim().toUpperCase());
			if (tempSql.toString().startsWith("CREATE")
					|| tempSql.toString().startsWith("INSERT")
					|| tempSql.toString().startsWith("DROP")) {
				return executeCreate(sql, statement);
			} else if (tempSql.toString().startsWith("UPDATE")) {
				return executeUpdate(sql, statement);
			}
			return executeQuery(sql, statement);
		} finally {
			if (con != null) {
				con.close();
			}
			logger.debug("executSql end");
		}

	}

	private List<List<String>> executeCreate(String sql, Statement statement)
			throws SQLException {
		logger.debug("executeCreate start");
		List<List<String>> rows = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("操作结果");
		rows.add(columns);
		try {
			statement.executeUpdate(sql);
			columns = new ArrayList<String>();
			columns.add("执行成功");
			rows.add(columns);
		} catch (Exception e) {
			columns = new ArrayList<String>();
			columns.add(e.getMessage());
			rows.add(columns);
		}
		logger.debug("executeCreate start");
		return rows;
	}

	private List<List<String>> executeUpdate(String sql, Statement statement)
			throws SQLException {
		logger.debug("executeUpdate start");
		int row = statement.executeUpdate(sql);
		List<List<String>> rows = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		columns.add("影响行数");
		rows.add(columns);
		columns = new ArrayList<String>();
		columns.add(String.valueOf(row));
		rows.add(columns);
		logger.debug("executeUpdate end");
		return rows;
	}

	private List<List<String>> executeQuery(String sql, Statement statement)
			throws SQLException {
		logger.debug("executeQuery start");
		ResultSet rs = statement.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount() + 1;
		List<List<String>> rows = new ArrayList<List<String>>();
		List<String> columns = new ArrayList<String>();
		for (int i = 1; i < columnCount; i++) {
			columns.add(rsmd.getColumnLabel(i));
		}
		rows.add(columns);
		while (rs.next()) {
			columns = new ArrayList<String>();
			for (int i = 1; i < columnCount; i++) {
				columns.add(rs.getString(i));
			}
			rows.add(columns);
		}
		logger.debug("executeQuery end");
		return rows;
	}
}
